package Data;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import Interface_Data.WholeFrameDataI;
import Util.DBUtil;
import Vo.WholeFrame;
import Vo.WholeFrameItem;

public class WholeFrameData implements WholeFrameDataI {
	DBUtil dbUtil = new DBUtil();
	Connection con = null;
	
	public void insertWholeFrame(WholeFrame wholeFrame) throws RemoteException {
		try {
			for (int i = 0; i < wholeFrame.getsize(); i++) {
				insertWholeFrameItem(wholeFrame.getWholeFrameItem(i));
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	@Override
	public WholeFrame returnWholeFrame() throws RemoteException {
		// TODO Auto-generated method stub
		try {
			con = dbUtil.getDataCon();
			String sql = "select * from wholeframe";
			PreparedStatement pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			WholeFrame temp = new WholeFrame();
			while (rs.next()) {
				WholeFrameItem wholeFrameItem = new WholeFrameItem();
				wholeFrameItem.setCourseModel(rs.getString("courseModel"));
				wholeFrameItem.setCourseType(rs.getString("courseType"));
				wholeFrameItem.setCatagory(rs.getString("catagory"));
				wholeFrameItem.setMinScore(rs.getInt("minScore"));
				wholeFrameItem.setMaxScore(rs.getInt("maxScore"));
				wholeFrameItem.setStartTerm(rs.getInt("startTerm"));
				wholeFrameItem.setEndTerm(rs.getInt("endTerm"));
				wholeFrameItem.setModelMinScore(rs.getInt("modelMinScore"));
				wholeFrameItem.setModelMaxScore(rs.getInt("modelMaxScore"));
				temp.addItem(wholeFrameItem);
			}
			return temp;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public void insertWholeFrameItem(WholeFrameItem wholeFrameItem)
			throws RemoteException {
		String sql = "insert into wholeframe values(?,?,?,?,?,?,?,?,?)";
		try {
			con = dbUtil.getDataCon();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, wholeFrameItem.getCourseModel());
			pstmt.setString(2, wholeFrameItem.getCourseType());
			pstmt.setString(3, wholeFrameItem.getCatagory());
			pstmt.setString(4, wholeFrameItem.getMinScore() + "");
			pstmt.setString(5, wholeFrameItem.getMaxScore() + "");
			pstmt.setString(6, wholeFrameItem.getStartTerm() + "");
			pstmt.setString(7, wholeFrameItem.getEndTerm() + "");
			pstmt.setString(8, wholeFrameItem.getModelMinScore() + "");
			pstmt.setString(9, wholeFrameItem.getModelMaxScore() + "");
			pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	@Override
	public void deleteWholeFrameItem(String catagory) throws RemoteException {
		String sql = "Delete from wholeframe where catagory=?";
		try {
			con = dbUtil.getDataCon();
			PreparedStatement ptmt = con.prepareStatement(sql);
			ptmt.setString(1, catagory);
			ptmt.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	public void modifyWholeFrameItem(WholeFrameItem wholeFrameItem)
			throws RemoteException {
		String sql = "update wholeframe set courseModel=?,courseType=?,minScore=?,maxScore=?, "
				+ "startTerm=?,endTerm=?,modelMinScore=?,modelMaxScore=? where catagory=?";
		try {
			con = dbUtil.getDataCon();
			PreparedStatement ptmt = con.prepareStatement(sql);
			ptmt.setString(1, wholeFrameItem.getCourseModel());
			ptmt.setString(2, wholeFrameItem.getCourseType());
			ptmt.setString(3, wholeFrameItem.getMinScore() + "");
			ptmt.setString(4, wholeFrameItem.getMaxScore() + "");
			ptmt.setString(5, wholeFrameItem.getStartTerm() + "");
			ptmt.setString(6, wholeFrameItem.getEndTerm() + "");
			ptmt.setString(7, wholeFrameItem.getModelMinScore() + "");
			ptmt.setString(8, wholeFrameItem.getModelMaxScore() + "");
			ptmt.setString(9, wholeFrameItem.getCatagory());
			ptmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	@Override
	public WholeFrame returnWholeFrame(String courseModel)
			throws RemoteException {
		// TODO Auto-generated method stub
		String sql = "select * from wholeFrame where courseModel = ?";
		try {
			con = dbUtil.getDataCon();
			PreparedStatement ptmt = con.prepareStatement(sql);
			ptmt.setString(1, courseModel);
			ResultSet rs = ptmt.executeQuery();
			WholeFrame wholeFrame = new WholeFrame();

			while (rs.next()) {
				String CourseModel = rs.getString("courseModel");
				String courseType = rs.getString("courseType");
				String catagory = rs.getString("catagory");
				int minScore = rs.getInt("minScore");
				int maxScore = rs.getInt("maxScore");
				int startTerm = rs.getInt("startTerm");
				int endTerm = rs.getInt("endTerm");
				int modelMinScore = rs.getInt("modelMinScore");
				int modelMaxScore = rs.getInt("modelMaxScore");
				wholeFrame.addItem(new WholeFrameItem(CourseModel, courseType,
						catagory, minScore, maxScore, startTerm, endTerm,
						modelMinScore, modelMaxScore));
			}
			return wholeFrame;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public ArrayList<String> returnCourseTypeList(String courseModel)
			throws RemoteException {
		// TODO Auto-generated method stub
		String sql = "Select * from wholeFrame  where courseModel = ?";
		try {
			con = dbUtil.getDataCon();
			PreparedStatement ptmt = con.prepareStatement(sql);
			 ptmt.setString(1, courseModel);
			ResultSet rs = ptmt.executeQuery();

			ArrayList<String> courseTypeList = new ArrayList<String>();

			while (rs.next()) {
			   courseTypeList.add(rs.getString("catagory")); 
              
			}

			return courseTypeList;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return null;
	}
	

}
